package tgc.edu.yzy.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import tgc.edu.yzy.cocApi.entity.form.BlackList;
import tgc.edu.yzy.controller.MyqqRobot;
import tgc.edu.yzy.custom.RedisUtil;
import tgc.edu.yzy.custom.TimeUtils;

public class XjMysqlJdbc {
	//链接熙酱服务器的 公共
	private static String a1="com.mysql.jdbc.Driver";
	private static String a2="jdbc:mysql://47.100.197.180:3306/xjpublic";
	private static String a3="qqrobot";
	private static String a4="936642284";

	public static Connection conn() {//链接数据库
		Connection connection=null;
		try {
			Class.forName(a1);//注册驱动
			connection =DriverManager.getConnection(a2,a3,a4);//建立链接
		}catch(Exception e) {
			System.out.println("数据库不存在，");
			return null;
		}
		return connection;
		
	}
	/***
	 * 获取随机鱼情建议
	 * @param type
	 * @return
	 */
	public String getYqjy(String type) {
		String sql = "select msg  FROM  yq  where  id  in  (select  t.id  from  (select id from yq where type='"+type+"' ORDER BY RAND() limit 1)  as  t )";
		Connection conn = conn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			ResultSet ex = pr.executeQuery();
			if(ex.next()) {
				return ex.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "";
		
		
	}
	/***
	 * 获取黑名单用户
	 * @return
	 */
	public Boolean lexiconNo() {
		String sql ="select * from lexicon_no";
		MyqqRobot.lexiconNo = new ArrayList<>();
		Connection conn = conn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			ResultSet ex = pr.executeQuery();
			while (ex.next()) {
				MyqqRobot.lexiconNo.add(ex.getString(2));
			}
			return true;
		} catch (SQLException e) {
		}
		return false;
	}
	/***
	 * 黑名单排序
	 * @return
	 */
	public static String blackList() {
		List<BlackList> blacklist = new XjMysqlJdbc().blacklist();
		MyqqRobot.myrobotqq  = new HashMap<String,Integer>(); 
		for (BlackList string : blacklist) {
			String date = string.getDate();
			if(date.equals("1")) {
				MyqqRobot.myrobotqq.put(string.getQqcode(), 1);
			}else if(date.equals("2")){
				MyqqRobot.myrobotqq.put(string.getQqcode(), 2);
			}else if(date.equals("3")){
				MyqqRobot.myrobotqq.put(string.getQqcode(), 3);
			}else if(date.equals("4")){
				MyqqRobot.myrobotqq.put(string.getQqcode(), 4);
			}else if(date.equals("5")){
				MyqqRobot.myrobotqq.put(string.getQqcode(), 5);
			}else {
				MyqqRobot.myrobotqq.put(string.getQqcode(), 4);
			}
		}
		return "黑名单数量："+MyqqRobot.myrobotqq.size();
	}
	/***、
	 * 查询所有黑名单
	 * @return
	 */
	public List<BlackList> blacklist(){
		List<BlackList> reust = new ArrayList<BlackList>();
		String sql ="select * from blacklist";
		Connection conn = conn();
		try {
			PreparedStatement prepareStatement = conn.prepareStatement(sql);
			ResultSet rs = prepareStatement.executeQuery();
			while(rs.next()) {
				BlackList black  = new BlackList();
				black.setQqcode(rs.getString(2));
				black.setDate(rs.getString(3));
				reust.add(black);
			}
			prepareStatement.close();
			conn.close();
			return reust;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/***
	 * 黑名单 列表
	 * @param message
	 * @return
	 */
	public boolean lexiconNo(String message) {
		String sql ="INSERT INTO lexicon_no VALUES (null,'"+message+"')";
		Connection conn = conn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			pr.execute();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
		
	}
	/***
	 * 违禁词
	 * @param qq
	 * @param message
	 * @return
	 */
	public boolean messageboard(String qq,String message) {
		String sql ="INSERT INTO messageboard VALUES (null,'"+message+"','"+qq+"','"+TimeUtils.getStringTime()+"')";
		Connection conn = conn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			pr.execute();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	
	/***
	 * 保存机器人添加的QQ好友
	 * @param robot 机器人QQ
	 * @param qqcode 操作者QQ
	 * @return
	 */
	public boolean setQQlist(String robot,String qqcode) {
		Connection conn = conn();
		try {
			String sqls ="select count(robot) from qqlist where robot='"+robot+"' and qqcode='"+qqcode+"'";
			PreparedStatement pr = conn.prepareStatement(sqls);
			ResultSet rs = pr.executeQuery();
			if(rs.next()) {
				int int1 = rs.getInt(1);
				if(int1==0) {
					String sql = "INSERT INTO qqlist VALUES (null,'"+robot+"','"+qqcode+"',null)";
					pr = conn.prepareStatement(sql);
					pr.execute();
					return true;
				}
			}
			pr.execute();
			return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	/***
	 * 获取是否重复好友
	 * @param qqcode
	 * @return
	 */
	public Integer QQList(String qqcode) {
		String sql = "SELECT COUNT(t.robot) from (select distinct robot,qqcode from qqlist where qqcode = '"+qqcode+"' and del !='1' and del !='2') t ";
		Connection conn = conn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			ResultSet ex = pr.executeQuery();
			if(ex.next()) {
				return ex.getInt(1);
			}
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	/***
	 * 获取当前机器人全部 好友
	 * @param robot
	 * @return
	 */
	public List<String> getQQList(String robot){
		String sql ="select qqcode from qqlist where robot='"+robot+"'";
		Connection conn = conn();
		try {
			List<String> array = new ArrayList<>();
			PreparedStatement pr = conn.prepareStatement(sql);
			ResultSet ex = pr.executeQuery();
			while(ex.next()) {
				array.add(ex.getString("qqcode"));
			}
			return array;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	
	public Integer remove(String qqcode) {
		String sql = "UPDATE qqlist SET del = '1' WHERE robot = '"+qqcode+"'";
		Connection conn = conn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			ResultSet ex = pr.executeQuery();
			if(ex.next()) {
				return ex.getInt(1);
			}
			ex.close();
			pr.close();
			conn.close();
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	public void UpdateQQList(List<String> delSql) {
		Connection conn = conn();
		try {
			PreparedStatement pr = null;
			for (String sql : delSql) {
				pr = conn.prepareStatement(sql);
				pr.execute();
			}
			pr.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		new XjMysqlJdbc().saveRedis("5825", null, 41);
	}
	/***
	 * 玩家获取聊天的时间
	 * @param qq
	 * @param utils
	 * @param time 天
	 */
	public void saveRedis(String qq, RedisUtil utils,Integer time) {
		Connection conn = conn();
		try {
			long expire2 = utils.getExpire("Tulin"+qq);
			long expi =(expire2+(time*86400));
			Integer expire = (int) expi;
			String endDd = TimeUtils.endDd(expire);
			String sqls ="select count(qqcode) from redis where qqcode='"+qq+"'";
			PreparedStatement pr = conn.prepareStatement(sqls);
			ResultSet rs = pr.executeQuery();
			if(rs.next()) {
				int int1 = rs.getInt(1);
				if(int1==0) {
					String sql = "INSERT INTO redis VALUES (null,'"+qq+"','"+endDd+"')";
					pr = conn.prepareStatement(sql);
				}else {
					String sql = "update redis set enddate='"+endDd+"' WHERE qqcode='"+qq+"'";
					pr = conn.prepareStatement(sql);
				}
			}
			pr.execute();
			utils.set("Tulin"+qq, "true", expi);
			pr.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
